package com.jiepos.mpos.biz.transaction.service;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.annotation.PostConstruct;
import javax.servlet.http.HttpServletRequest;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.jiepos.mpos.common.constant.DBConstant;
import com.jiepos.mpos.common.constant.DBConstant.Table;
import com.jiepos.mpos.common.constant.SystemConstant;
import com.jiepos.mpos.common.dao.LogAbstractDao;
import com.jiepos.mpos.common.entity.App_Ins_InfEntity;
import com.jiepos.mpos.common.entity.App_Log_AbstractEntity;
import com.jiepos.mpos.core.entity.Pagination;
import com.jiepos.mpos.core.service.BaseService;
import com.jiepos.mpos.core.util.DateUtil;
import com.jiepos.mpos.core.util.ExcelUtil;
import com.jiepos.mpos.core.util.StringUtils;

@Service
public class AppLogAbstractService extends BaseService {

	/** 导出excel文件表头*/
	private static List<String> excelHead = null;
	/** sheet页名称*/
	private static String EXCEL_LOGABSTRACT_NAME = "提现流水";

	private static String EXCEL_LOGABSTRACT_SUM_NAME = "提现手续费汇总";
	/** 导出excel文件表头*/
	private static List<String> excelHeadSum = null;

	@Autowired
	private LogAbstractDao abstractDao;
	
	/**
	 * 获取提现申请Json
	 * @param pagination
	 * @param request
	 * @param abstractEntity
	 * @return
	 */
	public Map getLogAbstractListJson(Pagination pagination,HttpServletRequest request,App_Log_AbstractEntity abstractEntity,
			String custom_no,String phone,String name,String settle_acct,String apply_startDate,
									  String apply_endDate,String audit_startDate,String audit_endDate,String rspcode,String stlcode){
		
		StringBuffer sb = this.getInitSql("t1");
		List<String> list = new ArrayList<>();
		sb.append(" ,T1.ABSTRACT_UUID,T1.INS_UUID,T4.INS_CN_NM,T1.APPLY_NO,T1.CUSTOM_UUID,T2.CUSTOM_NO,T5.SERVICE_NO||'-'||T5.SERVICE_NAME AS SERVICE_NAME,T6.PARAM_NO||'-'||T6.PARAM_NAME AS PARAM_NAME,")
			.append("CONCAT(RPAD (SUBSTR (T2.login_id, 1, 3),LENGTH (T2.login_id) - 4,'*'),SUBSTR (T2.login_id,LENGTH (T2.login_id) - 3,4))||'-'||T2.NAME AS NAME,")
			.append(" TO_CHAR(T1.APPLY_AMT,'fm9999999990.00') as APPLY_AMT,TO_CHAR(T1.WITHDRAW_FEE,'fm9999999990.00') as WITHDRAW_FEE,TO_CHAR(T1.STL_AMT,'fm9999999990.00') as STL_AMT,")
			.append(" CONCAT (RPAD (SUBSTR (t3.settle_acct, 1, 6),LENGTH (t3.settle_acct) - 4,'*'),SUBSTR (t3.settle_acct,LENGTH (t3.settle_acct) - 3,4)) AS SETTLE_ACCT,")
			.append(" TO_CHAR(TO_DATE (T1.APPLY_DT || T1.APPLY_TS,'yyyy-MM-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') AS APPLY_DT,")
			.append(" (select code_val||'-'||code_desc from T_APP_CODE_LIST where code_val = t1.ACCOUNT_TYPE and code_tp = 'ACCOUNT_TYPE') as ACCOUNT_TYPE,")
			.append(" (select code_val||'-'||code_desc from T_APP_CODE_LIST where code_val = t1.AUDIT_STLFLAG and code_tp = 'STLST') as AUDIT_STLFLAG,")
			.append(" TO_CHAR(TO_DATE (T1.AUDIT_DT || T1.AUDIT_TS,'yyyy-MM-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') AS AUDIT_DT,")
			.append(" nvl2(T1.RSP_CODE,T1.RSP_CODE || '-' || T1.RSP_MSG ,'') AS RSP_CODE,nvl2(T1.STL_CODE,T1.STL_CODE || '-' || T1.STL_MSG ,'') AS STL_CODE FROM ").append(Table.T_APP_LOG_ABSTRACT).append(" t1 ")
			.append(" left join ").append(Table.T_APP_CUSTOM_INF).append(" t2 on t1.custom_uuid = t2.custom_uuid ")
			.append(" left join ").append(Table.T_APP_CUSTOM_BANKCARD).append(" t3 on t1.custom_bankcard_uuid = t3.bankcard_uuid ")
			.append(" left join ").append(Table.T_APP_INS_INF).append(" t4 on t1.ins_uuid = t4.ins_uuid ")
			.append(" left join ").append(Table.T_APP_SERVICE_INF).append(" t5 on t1.service_uuid = t5.service_uuid ")
			.append(" left join ").append(Table.T_APP_SERVICE_PARAM).append(" t6 on t1.service_param_uuid = t6.service_param_uuid where 1=1 ");
		if(!StringUtils.isBlank(custom_no)){
			sb.append(" and t2.custom_no like ?");
			list.add("%"+custom_no+"%");
		}
		if(!StringUtils.isBlank(phone)){
			sb.append(" and t2.phone like ?");
			list.add("%"+phone+"%");
		}
		if(!StringUtils.isBlank(name)){
			sb.append(" and t2.name like ?");
			list.add("%"+name+"%");
		}
		if(!StringUtils.isBlank(settle_acct)){
			sb.append(" and t3.settle_acct like ?");
			list.add("%"+settle_acct+"%");
		}
		if(!StringUtils.isBlank(apply_startDate)){
			sb.append(" and t1.apply_dt >= ?");
			Date date = DateUtil.strToDate(apply_startDate, DateUtil.FORMAT_DATE_1);
			list.add(DateUtil.dateToStr(date, "yyyyMMdd"));
		}
		if(!StringUtils.isBlank(apply_endDate)){
			sb.append(" and t1.apply_dt <= ?");
			Date date = DateUtil.strToDate(apply_endDate,DateUtil.FORMAT_DATE_1);
			list.add(DateUtil.dateToStr(date,"yyyyMMdd"));
		}else {
			sb.append(" and t1.apply_dt <= ?");
			Date date = DateUtil.strToDate(apply_endDate,DateUtil.FORMAT_DATE_1);
			list.add(DateUtil.dateToStr(date,"yyyyMMdd"));
		}
		if(!StringUtils.isBlank(audit_startDate)){
			sb.append(" and t1.audit_dt >= ?");
			Date date = DateUtil.strToDate(audit_startDate, DateUtil.FORMAT_DATE_1);
			list.add(DateUtil.dateToStr(date, "yyyyMMdd"));
		}
		if(!StringUtils.isBlank(audit_endDate)){
			sb.append(" and t1.audit_dt <= ?");
			Date date = DateUtil.strToDate(audit_endDate,DateUtil.FORMAT_DATE_1);
			list.add(DateUtil.dateToStr(date,"yyyyMMdd"));
		}
		if(!StringUtils.isBlank(abstractEntity.getAccount_type())){
			sb.append(" and t1.account_type = ?");
			list.add(abstractEntity.getAccount_type());
		}
		if(!StringUtils.isBlank(abstractEntity.getAudit_stlflag())){
			sb.append(" and t1.audit_stlflag = ?");
			list.add(abstractEntity.getAudit_stlflag());
		}
		//提现响应码
		if (!StringUtils.isBlank(rspcode)) {
			if(!rspcode.equals(SystemConstant.RSPCODE_ERROR)){
				sb.append(" and t1.RSP_CODE = ?");
				list.add(rspcode);
			}else{
				sb.append(" and t1.RSP_CODE != 'XX' and t1.RSP_CODE != '00'");
			}
		}
        //清算响应码
        if (!StringUtils.isBlank(stlcode)) {
            if(stlcode.equalsIgnoreCase(SystemConstant.SUCCESS)){
                sb.append(" and t1.STL_CODE = ?");
            }else{
                sb.append(" and t1.STL_CODE <> ?");
            }
            list.add(SystemConstant.SUCCESS);
        }
		pagination.queryString = sb.toString();
		pagination = super.getInsAuthSql(request, pagination, "t2", null);
		pagination = this.getOrderSql(pagination, "t1");
		return abstractDao.getPaginationMap(pagination, "t1", list);
	}

	/**
	 * 导出
	 * @param abstractEntity
	 * @param custom_no
	 * @param phone
	 * @param name
	 * @param settle_acct
	 * @param apply_startDate
	 * @param apply_endDate
	 * @param audit_startDate
	 * @param audit_endDate
	 * @param rspcode
	 * @return
	 */
	public String executeSaveLogAbstractList(String fileName , App_Log_AbstractEntity abstractEntity,
											 String custom_no, String phone, String name, String settle_acct,
											 String apply_startDate, String apply_endDate, String audit_startDate,
											 String audit_endDate, String rspcode, String stlcode) {
		List<Map> dataList = abstractDao.queryForList(getQueryLogSql(abstractEntity,custom_no,phone, name, settle_acct,
				 apply_startDate,  apply_endDate,  audit_startDate,
				 audit_endDate,  rspcode, stlcode),getParamsSql(abstractEntity,custom_no,
				phone,name,settle_acct,apply_startDate,apply_endDate,audit_startDate,audit_endDate,rspcode, stlcode));
		return ExcelUtil.writeExcel(fileName, EXCEL_LOGABSTRACT_NAME, excelHead, dataList);
	}


	/**
	 *组装sql语句
	 * @return
	 */
	private String getQueryLogSql(App_Log_AbstractEntity abstractEntity,
								  String custom_no, String phone, String name, String settle_acct,
								  String apply_startDate, String apply_endDate, String audit_startDate,
								  String audit_endDate, String rspcode, String stlcode){
		StringBuffer sb = new StringBuffer();
		sb.append("select T1.ABSTRACT_UUID, TO_CHAR(TO_DATE (T1.APPLY_DT || T1.APPLY_TS,'yyyy-MM-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') AS APPLY_DT, T1.APPLY_NO,T2.CUSTOM_NO,")
				.append("CONCAT(RPAD (SUBSTR (T2.login_id, 1, 3),LENGTH (T2.login_id) - 4,'*'),SUBSTR (T2.login_id,LENGTH (T2.login_id) - 3,4))||'-'||T2.NAME AS NAME,")
				.append("T4.INS_CN_NM,")
				.append(" (select code_val||'-'||code_desc from T_APP_CODE_LIST where code_val = t1.ACCOUNT_TYPE and code_tp = 'ACCOUNT_TYPE') as ACCOUNT_TYPE,")
				.append(" T5.SERVICE_NO||'-'||T5.SERVICE_NAME AS SERVICE_NAME,T6.PARAM_NO||'-'||T6.PARAM_NAME AS PARAM_NAME, ")
				.append(" CONCAT (RPAD (SUBSTR (t3.settle_acct, 1, 6),LENGTH (t3.settle_acct) - 4,'*'),SUBSTR (t3.settle_acct,LENGTH (t3.settle_acct) - 3,4)) AS SETTLE_ACCT,")
				.append(" TO_CHAR(T1.APPLY_AMT,'fm9999999990.00') as APPLY_AMT,TO_CHAR(T1.WITHDRAW_FEE,'fm9999999990.00') as WITHDRAW_FEE,TO_CHAR(T1.STL_AMT,'fm9999999990.00') as STL_AMT,")
				.append(" TO_CHAR(TO_DATE (T1.AUDIT_DT || T1.AUDIT_TS,'yyyy-MM-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') AS AUDIT_DT,")
				.append(" (select code_val||'-'||code_desc from T_APP_CODE_LIST where code_val = t1.AUDIT_STLFLAG and code_tp = 'STLST') as AUDIT_STLFLAG,")
				.append(" nvl2(T1.RSP_CODE,T1.RSP_CODE || '-' || T1.RSP_MSG ,'') AS RSP_CODE, ")
				.append(" TO_CHAR(TO_DATE (T1.APPLY_DT || T1.APPLY_TS,'yyyy-MM-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') AS APPLY_DT,")
				.append(" nvl2(T1.STL_CODE,T1.STL_CODE || '-' || T1.STL_MSG ,'') AS STL_CODE FROM ")
				.append(Table.T_APP_LOG_ABSTRACT).append(" t1 ")
				.append(" left join ").append(Table.T_APP_CUSTOM_INF).append(" t2 on t1.custom_uuid = t2.custom_uuid ")
				.append(" left join ").append(Table.T_APP_CUSTOM_BANKCARD).append(" t3 on t1.custom_bankcard_uuid = t3.bankcard_uuid ")
				.append(" left join ").append(Table.T_APP_INS_INF).append(" t4 on t1.ins_uuid = t4.ins_uuid ")
				.append(" left join ").append(Table.T_APP_SERVICE_INF).append(" t5 on t1.service_uuid = t5.service_uuid ")
				.append(" left join ").append(Table.T_APP_SERVICE_PARAM).append(" t6 on t1.service_param_uuid = t6.service_param_uuid where 1=1 ");
		if(!StringUtils.isBlank(custom_no)){
			sb.append(" and t2.custom_no like ?");
		}
		if(!StringUtils.isBlank(phone)){
			sb.append(" and t2.phone like ?");
		}
		if(!StringUtils.isBlank(name)){
			sb.append(" and t2.name like ?");
		}
		if(!StringUtils.isBlank(settle_acct)){
			sb.append(" and t3.settle_acct like ?");
		}
		if(!StringUtils.isBlank(apply_startDate)){
			sb.append(" and t1.apply_dt >= ?");
		}
		if(!StringUtils.isBlank(apply_endDate)){
			sb.append(" and t1.apply_dt <= ?");
		}
		if(!StringUtils.isBlank(audit_startDate)){
			sb.append(" and t1.audit_dt >= ?");
		}
		if(!StringUtils.isBlank(audit_endDate)){
			sb.append(" and t1.audit_dt <= ?");
		}
		if(!StringUtils.isBlank(abstractEntity.getAccount_type())){
			sb.append(" and t1.account_type = ?");
		}
		if(!StringUtils.isBlank(abstractEntity.getAudit_stlflag())){
			sb.append(" and t1.audit_stlflag = ?");

		}
		//提现响应码
		if (!StringUtils.isBlank(rspcode)) {
			if(!rspcode.equals(SystemConstant.RSPCODE_ERROR)){
				sb.append(" and t1.RSP_CODE = ?");
			}else{
				sb.append(" and t1.RSP_CODE != 'XX' and t1.RSP_CODE != '00'");
			}
		}
		//清算响应码
        if (!StringUtils.isBlank(stlcode)) {
            if(stlcode.equalsIgnoreCase(SystemConstant.SUCCESS)){
                sb.append(" and t1.STL_CODE = ? ");
            }else{
                sb.append(" and t1.STL_CODE <> ?");
            }
        }
		sb.append(" ORDER BY t1.REC_CRT_TS DESC ");
		return sb.toString();
	}

	/**
	 * 组装参数
	 * @param abstractEntity
	 * @param custom_no
	 * @param phone
	 * @param name
	 * @param settle_acct
	 * @param apply_startDate
	 * @param apply_endDate
	 * @param audit_startDate
	 * @param audit_endDate
	 * @param rspcode
	 * @return
	 */
	private  List<String> getParamsSql(App_Log_AbstractEntity abstractEntity,
								String custom_no, String phone, String name, String settle_acct,
								String apply_startDate, String apply_endDate, String audit_startDate,
								String audit_endDate, String rspcode, String stlcode){

		List<String> list = new ArrayList<>();
		if(!StringUtils.isBlank(custom_no)){
			list.add("%"+custom_no+"%");
		}
		if(!StringUtils.isBlank(phone)){
			list.add("%"+phone+"%");
		}
		if(!StringUtils.isBlank(name)){
			list.add("%"+name+"%");
		}
		if(!StringUtils.isBlank(settle_acct)){
			list.add("%"+settle_acct+"%");
		}
		if(!StringUtils.isBlank(apply_startDate)){
			Date date = DateUtil.strToDate(apply_startDate, DateUtil.FORMAT_DATE_1);
			list.add(DateUtil.dateToStr(date, "yyyyMMdd"));
		}
		if(!StringUtils.isBlank(apply_endDate)){
			Date date = DateUtil.strToDate(apply_endDate,DateUtil.FORMAT_DATE_1);
			list.add(DateUtil.dateToStr(date,"yyyyMMdd"));
		}
		if(!StringUtils.isBlank(audit_startDate)){
			Date date = DateUtil.strToDate(audit_startDate, DateUtil.FORMAT_DATE_1);
			list.add(DateUtil.dateToStr(date, "yyyyMMdd"));
		}
		if(!StringUtils.isBlank(audit_endDate)){
			Date date = DateUtil.strToDate(audit_endDate,DateUtil.FORMAT_DATE_1);
			list.add(DateUtil.dateToStr(date,"yyyyMMdd"));
		}
		if(!StringUtils.isBlank(abstractEntity.getAccount_type())){
			list.add(abstractEntity.getAccount_type());
		}
		if(!StringUtils.isBlank(abstractEntity.getAudit_stlflag())){
			list.add(abstractEntity.getAudit_stlflag());
		}
		//提现响应码
		if (!StringUtils.isBlank(rspcode)) {
			if(!rspcode.equals(SystemConstant.RSPCODE_ERROR)){
				list.add(rspcode);
			}
		}
		//清算响应码
        if (!StringUtils.isBlank(stlcode)) {
        	list.add(SystemConstant.SUCCESS);
        }
		return list;
	}


	@PostConstruct
	private void init(){
		excelHead = new ArrayList<>();
		excelHead.add("申请时间");
		excelHead.add("申请单号");
		excelHead.add("会员编号");
		excelHead.add("会员名称");
		excelHead.add("所属机构");
		excelHead.add("账户类型");
		excelHead.add("业务名称");
		excelHead.add("业务参数");
		excelHead.add("到账银行卡号");
		excelHead.add("申请金额");
		excelHead.add("提现手续费");
		excelHead.add("清算金额");
		excelHead.add("清算时间");
		excelHead.add("清算标志");
		excelHead.add("提现响应码");
		excelHead.add("清算响应码");
	}


	/**
	 * 获取手续费汇总Json
	 * @param pagination
	 * @param request
	 * @param abstractEntity
	 * @return
	 */
	public Map getLogAbstractSumListJson(Pagination pagination, HttpServletRequest request, App_Log_AbstractEntity abstractEntity, App_Ins_InfEntity infEntity, String apply_startDate, String apply_endDate) {


		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append(" SELECT  max(TO_CHAR(TO_DATE(t1.apply_dt, 'YYYY-MM-DD'), 'yyyy-mm-dd')) as apply_dt, ")
				.append("        max(t2.ins_cd || '-' || t2.ins_cn_nm) as inst_name, ")
				.append("        sum(TO_CHAR(NVL(t1.withdraw_fee, 0.00), 'fm9999999990.00')) AS withdraw_fee, ")
				.append("        max(t1.rsp_code || '-' || t1.rsp_msg) as rsp_msg ")
				.append("   FROM "+Table.T_APP_LOG_ABSTRACT+" t1 ")
				.append("   left join "+Table.T_APP_INS_INF+" t2 ")
				.append("     on t1.ins_uuid = t2.ins_uuid ")
				.append("  where 1 = 1 ")
				.append("    and t1.rsp_code = '00' ");
		List<String> list = new ArrayList<>();
		//提现日期
		if(!StringUtils.isBlank(apply_startDate)){
			sqlBuf.append(" and t1.apply_dt >= ?");
			Date date = DateUtil.strToDate(apply_startDate, DateUtil.FORMAT_DATE_1);
			list.add(DateUtil.dateToStr(date, "yyyyMMdd"));
		}else{
			sqlBuf.append("and t1.apply_dt >= ? ");
			list.add(DateUtil.getCurrDateStr());
		}
		if(!StringUtils.isBlank(apply_endDate)){
			sqlBuf.append(" and t1.apply_dt <= ?");
			Date date = DateUtil.strToDate(apply_endDate,DateUtil.FORMAT_DATE_1);
			list.add(DateUtil.dateToStr(date,"yyyyMMdd"));
		}else {
			sqlBuf.append(" and t1.apply_dt <= ?");
			list.add(DateUtil.getCurrDateStr());
		}
		//机构号
		if (!StringUtils.isBlank(infEntity.getIns_cd())) {
			sqlBuf.append( " and t2.ins_cd like ? " );
			list.add("%" + infEntity.getIns_cd() + "%");
		}
		//机构名称
		if (!StringUtils.isBlank(infEntity.getIns_cn_nm())) {
			sqlBuf.append( " and t2.ins_cn_nm like ? " );
			list.add("%" + infEntity.getIns_cn_nm() + "%");
		}
		pagination.queryString = sqlBuf.toString();
		pagination = super.getInsAuthSql(request, pagination, "t2", null);
		pagination = this.getOrderApplyDtSql(pagination, "t1");
		return abstractDao.getPaginationMap(pagination, "t1", list);
	}


	/**
	 * 导出
	 * @param fileName
	 * @param abstractEntity
	 * @param apply_startDate
	 * @param apply_endDate
	 * @return
	 */
	public String executeSaveLogAbstractSumList(HttpServletRequest request,String fileName, App_Log_AbstractEntity abstractEntity,App_Ins_InfEntity infEntity,String apply_startDate, String apply_endDate) {

		List<Map> dataList = abstractDao.queryForList(getQueryLogSumSql(request,abstractEntity,infEntity,
				apply_startDate,  apply_endDate),getParamsSumSql(abstractEntity,infEntity,apply_startDate,apply_endDate));
		return ExcelUtil.writeExcel(fileName, EXCEL_LOGABSTRACT_SUM_NAME, excelHeadSum, dataList);
	}

	private List getParamsSumSql(App_Log_AbstractEntity abstractEntity, App_Ins_InfEntity infEntity,String apply_startDate, String apply_endDate) {
		List<String> list = new ArrayList<>();
		//提现日期
		if(!StringUtils.isBlank(apply_startDate)){
			Date date = DateUtil.strToDate(apply_startDate, DateUtil.FORMAT_DATE_1);
			list.add(DateUtil.dateToStr(date, "yyyyMMdd"));
		}else{
			list.add(DateUtil.getCurrDateStr());
		}
		if(!StringUtils.isBlank(apply_endDate)){
			Date date = DateUtil.strToDate(apply_endDate,DateUtil.FORMAT_DATE_1);
			list.add(DateUtil.dateToStr(date,"yyyyMMdd"));
		}else {
			list.add(DateUtil.getCurrDateStr());
		}
		//机构号
		if (!StringUtils.isBlank(infEntity.getIns_cd())) {
			list.add("%" + infEntity.getIns_cd() + "%");
		}
		//机构名称
		if (!StringUtils.isBlank(infEntity.getIns_cn_nm())) {
			list.add("%" + infEntity.getIns_cn_nm() + "%");
		}
		return list;
	}

	private String getQueryLogSumSql(HttpServletRequest request,App_Log_AbstractEntity abstractEntity,App_Ins_InfEntity infEntity, String apply_startDate, String apply_endDate) {
		String ins_uuid = String.valueOf(request.getSession().getAttribute(SystemConstant.SESSION_LOGIN_INS_UUID));
		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append(" SELECT max(T1.ABSTRACT_UUID) as ABSTRACT_UUID, max(TO_CHAR(TO_DATE(t1.apply_dt, 'YYYY-MM-DD'), 'yyyy-mm-dd')) as apply_dt, ")
				.append("        max(t2.ins_cd || '-' || t2.ins_cn_nm) as inst_name, ")
				.append("        sum(TO_CHAR(NVL(t1.withdraw_fee, 0.00), 'fm9999999990.00')) AS withdraw_fee ")
				.append("   FROM "+Table.T_APP_LOG_ABSTRACT+" t1 ")
				.append("   left join "+Table.T_APP_INS_INF+" t2 ")
				.append("     on t1.ins_uuid = t2.ins_uuid ")
				.append("  where 1 = 1 ")
				.append("    and t1.rsp_code = '00' ");
		List<String> list = new ArrayList<>();
		//提现日期
		if(!StringUtils.isBlank(apply_startDate)){
			sqlBuf.append(" and t1.apply_dt >= ?");
		}else{
			sqlBuf.append("and t1.apply_dt >= ? ");
		}
		if(!StringUtils.isBlank(apply_endDate)){
			sqlBuf.append(" and t1.apply_dt <= ?");
		}else {
			sqlBuf.append(" and t1.apply_dt <= ?");
		}
		//机构号
		if (!StringUtils.isBlank(infEntity.getIns_cd())) {
			sqlBuf.append( " and t2.ins_cd like ? " );
		}
		//机构名称
		if (!StringUtils.isBlank(infEntity.getIns_cn_nm())) {
			sqlBuf.append( " and t2.ins_cn_nm like ? " );

		}
		sqlBuf.append("  and  t2.ins_uuid in ");
		sqlBuf.append(" (select tt.ins_uuid from "+ DBConstant.Table.T_APP_INS_INF+" tt ");
		sqlBuf.append(" start with tt.ins_uuid='" + ins_uuid + "'");
		sqlBuf.append(" connect by prior tt.ins_uuid = tt.sup_ins_uuid)");
		sqlBuf.append("  group by t1.apply_dt ")
				.append(" order by t1.apply_dt desc");
		return sqlBuf.toString();

	}

	@PostConstruct
	private void execute(){
		excelHeadSum = new ArrayList<>();
		excelHeadSum.add("申请时间");
		excelHeadSum.add("所属机构");
		excelHeadSum.add("提现手续费总额");
	}

	/**
	 * 拼装日期排序sql
	 *
	 * @param pagination
	 * @return
	 */
	public Pagination getOrderApplyDtSql(Pagination pagination, String prefix) {
		if (!StringUtils.isBlank(prefix)) {
			prefix = prefix + ".";
		} else {
			prefix = "";
		}
		pagination.queryString = pagination.queryString + " and " + prefix + SystemConstant.REC_ST + "!='"
				+ SystemConstant.DEL_STATUS + "' group by t1.apply_dt  order by " + prefix + "apply_dt desc";
		log.debug("pagination.queryString : " + pagination.queryString);
		return pagination;
	}

	public Map getLogAbstractSumTotal(HttpServletRequest request, App_Log_AbstractEntity abstractEntity, App_Ins_InfEntity infEntity, String apply_startDate, String apply_endDate) {
		String ins_uuid = String.valueOf(request.getSession().getAttribute(SystemConstant.SESSION_LOGIN_INS_UUID));
		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append(" SELECT   ")
				.append("        sum(TO_CHAR(NVL(t1.withdraw_fee, 0.00), 'fm9999999990.00')) AS withdraw_fee_sum ")
				.append("   FROM "+Table.T_APP_LOG_ABSTRACT+" t1 ")
				.append("   left join "+Table.T_APP_INS_INF+" t2 ")
				.append("     on t1.ins_uuid = t2.ins_uuid ")
				.append("  where 1 = 1 ")
				.append("    and t1.rsp_code = '00' ");
		List<String> list = new ArrayList<>();
		//提现日期
		if(!StringUtils.isBlank(apply_startDate)){
			sqlBuf.append(" and t1.apply_dt >= ?");
			Date date = DateUtil.strToDate(apply_startDate, DateUtil.FORMAT_DATE_1);
			list.add(DateUtil.dateToStr(date, "yyyyMMdd"));
		}else{
			sqlBuf.append("and t1.apply_dt >= ? ");
			list.add(DateUtil.getCurrDateStr());
		}
		if(!StringUtils.isBlank(apply_endDate)){
			sqlBuf.append(" and t1.apply_dt <= ?");
			Date date = DateUtil.strToDate(apply_endDate,DateUtil.FORMAT_DATE_1);
			list.add(DateUtil.dateToStr(date,"yyyyMMdd"));
		}else {
			sqlBuf.append(" and t1.apply_dt <= ?");
			list.add(DateUtil.getCurrDateStr());
		}
		//机构号
		if (!StringUtils.isBlank(infEntity.getIns_cd())) {
			sqlBuf.append( " and t2.ins_cd like ? " );
			list.add("%" + infEntity.getIns_cd() + "%");
		}
		//机构名称
		if (!StringUtils.isBlank(infEntity.getIns_cn_nm())) {
			sqlBuf.append( " and t2.ins_cn_nm like ? " );
			list.add("%" + infEntity.getIns_cn_nm() + "%");
		}
		sqlBuf.append("  and  t2.ins_uuid in ");
		sqlBuf.append(" (select tt.ins_uuid from "+ DBConstant.Table.T_APP_INS_INF+" tt ");
		sqlBuf.append(" start with tt.ins_uuid='" + ins_uuid + "'");
		sqlBuf.append(" connect by prior tt.ins_uuid = tt.sup_ins_uuid)")
				.append(" order by t1.apply_dt desc");
		String sql = sqlBuf.toString();

		log.debug("getLogCustomCommProfitSumTotal queryString: " + sql);
		return (Map)abstractDao.queryForList(sql, list).get(0);
	}
}
 